package cn.gotom.pinggu.service;

import java.io.UnsupportedEncodingException;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

import org.apache.log4j.Logger;

import cn.gotom.core.dao.JdbcUtils;
import cn.gotom.pinggu.vo.Dmb;
import cn.gotom.pinggu.vo.Nsrxx;
import cn.gotom.pinggu.vo.Sbsj;
import cn.gotom.pinggu.vo.YHXXB;
import cn.gotom.util.StringUtils;

public class DataServiceImpl implements DataService
{
	private final Logger log = Logger.getLogger(getClass());

	private String encoding(String str)
	{
		String enStr = StringUtils.encoding(str, "cp850", "GBK");
		return enStr;
	}

	@Override
	public Nsrxx getNsrxxByNsrdm(String nsrdm)
	{
		PreparedStatement stmt = null;
		ResultSet rs = null;
		Nsrxx nsrxx = new Nsrxx();
		try
		{
			log.error("getNsrxxByNsrdm");
			Connection conn = JdbcUtils.currentConnection();
			stmt = conn.prepareStatement("use HuJi");
			stmt.executeUpdate();
			stmt.close();
			String sql = "select A.NSRDM,A.NSRMC,A.SBH_NSR,A.XM_FDDBR,A.DZ_JY,A.DH_DZ_JY,A.XM_BSR,A.YZBM_DZ_JY,A.ZZJGDM,B.NTZGY ";
			sql += "from SWDJB A ,NSRBCGLZL B where A.NSRDM = B.NSRDM and A.DJLX = '11' and A.NSRDM = ?";
			stmt = conn.prepareStatement(sql);
			stmt.setString(1, nsrdm);
			rs = stmt.executeQuery();
			if (rs.next())
			{
				nsrxx.setNsrdm(encoding(rs.getString("NSRDM")));
				nsrxx.setNsrmc(encoding(rs.getString("NSRMC")));
				// nsrxx.setNsrmc(encoding(rs.getString("SBH_NSR")));
				nsrxx.setFddbr(encoding(rs.getString("XM_FDDBR")));
				nsrxx.setJydz(encoding(rs.getString("DZ_JY")));
				nsrxx.setLxdh(encoding(rs.getString("DH_DZ_JY")));
				nsrxx.setLxr(encoding(rs.getString("XM_BSR")));
				nsrxx.setNtzgy(encoding(rs.getString("NTZGY")));
				nsrxx.setYzbm(encoding(rs.getString("YZBM_DZ_JY")));
				// nsrxx.setZxbj(rs.getString(9));
				nsrxx.setZzjgdm(encoding(rs.getString("ZZJGDM")));
			}
		}
		catch (Throwable e)
		{
			nsrxx.setNsrdm(nsrdm);
			nsrxx.setNsrmc("读数据失败");
			log.error(e.getMessage(), e);
		}
		finally
		{
			try
			{
				if (rs != null)
					rs.close();
				if (stmt != null)
					stmt.close();
			}
			catch (SQLException e)
			{
				log.error(e.getMessage(), e);
			}
		}
		return nsrxx;
	}

	@Override
	public Nsrxx getNsrxxByNsrmc(String nsrmc)
	{
		// TODO Auto-generated method stub
		return null;
	}

	@Override
	public Sbsj getSbsjByNsrdm(String nsrdm, int nd, int yf)
	{
		PreparedStatement stmt = null;
		ResultSet rs = null;
		Sbsj sbsj = new Sbsj();
		try
		{
			Connection conn = JdbcUtils.currentConnection();
			stmt = conn.prepareStatement("use ShenBao");
			stmt.executeUpdate();
			stmt.close();
			StringBuilder sb = new StringBuilder();
			sb.append("select NSHDM,NSHMC,SBSJ,QRQ,ZRQ,SYSL,sum(YNSE) as YNSE,sum(RKS) as RKS");
			sb.append(" from SBZBJG");
			sb.append(" where NSHDM = '" + nsrdm + "'");
			sb.append(" and year(QRQ) = " + nd);
			sb.append(" and month(QRQ) = " + yf);
			sb.append(" group by QRQ,NSHDM,NSHMC,SBSJ,QRQ,ZRQ,SYSL");

			// String sql = "select NSHDM,NSHMC,SBSJ,QRQ,ZRQ,SYSL,YNSE,RKS from SBZBJG where NSHDM = ?";
			stmt = conn.prepareStatement(sb.toString());
			// stmt.setString(1, nsrdm);
			rs = stmt.executeQuery();
			if (rs.next())
			{
				sbsj.setNsrdm(encoding(rs.getString(1)));
				sbsj.setNsrmc(encoding(rs.getString(2)));
				sbsj.setSbsj(rs.getDate(3));
				sbsj.setQrq(rs.getDate(4));
				sbsj.setZrq(rs.getDate(5));
				sbsj.setSysl(rs.getFloat(6));
				sbsj.setYnse(rs.getFloat(7));
				sbsj.setRks(rs.getFloat(8));
			}
		}
		catch (Throwable e)
		{
			log.error(e.getMessage(), e);
		}
		finally
		{
			try
			{
				if (rs != null)
					rs.close();
				if (stmt != null)
					stmt.close();
			}
			catch (SQLException e)
			{
				log.error(e.getMessage(), e);
			}
		}
		return sbsj;
	}

	@Override
	public Sbsj getSbsjByNsrdm(String nsrdm)
	{
		PreparedStatement stmt = null;
		ResultSet rs = null;
		Sbsj sbsj = new Sbsj();
		try
		{
			Connection conn = JdbcUtils.currentConnection();
			stmt = conn.prepareStatement("use ShenBao");
			stmt.executeUpdate();
			stmt.close();
			String sql = "select NSHDM,NSHMC,SBSJ,QRQ,ZRQ,SYSL,YNSE from SBZBJG where NSHDM = ?";
			stmt = conn.prepareStatement(sql);
			stmt.setString(1, nsrdm);
			rs = stmt.executeQuery();
			if (rs.next())
			{
				sbsj.setNsrdm(encoding(rs.getString(1)));
				sbsj.setNsrmc(encoding(rs.getString(2)));
				sbsj.setSbsj(rs.getDate(3));
				sbsj.setQrq(rs.getDate(4));
				sbsj.setZrq(rs.getDate(5));
				sbsj.setSysl(rs.getFloat(6));
				sbsj.setYnse(rs.getFloat(7));
			}
			sql = "select RKS,RKRQ from SJYDB where NSHDM = ?";
			stmt = conn.prepareStatement(sql);
			stmt.setString(1, nsrdm);
			rs = stmt.executeQuery();
			if (rs.next())
			{
				sbsj.setRks(rs.getFloat(1));
				sbsj.setRkrq(rs.getDate(2));
			}
		}
		catch (Throwable e)
		{
			log.error(e.getMessage(), e);
		}
		finally
		{
			try
			{
				if (rs != null)
					rs.close();
				if (stmt != null)
					stmt.close();
			}
			catch (SQLException e)
			{
				log.error(e.getMessage(), e);
			}
		}
		return sbsj;
	}

	@Override
	public Dmb getDmbByFj(String fj)
	{
		PreparedStatement stmt = null;
		ResultSet rs = null;
		Dmb dmb = new Dmb();
		try
		{
			Connection conn = JdbcUtils.currentConnection();
			stmt = conn.prepareStatement("use systbase");
			stmt.executeUpdate();
			stmt.close();
			String sql = "select FJ,CQ,DM,MS from FJDMB where FJ = ?";
			stmt = conn.prepareStatement(sql);
			stmt.setString(1, fj);
			rs = stmt.executeQuery();
			if (rs.next())
			{
				dmb.setFj(encoding(rs.getString(1)));
				dmb.setCq(encoding(rs.getString(2)));
				dmb.setDm(encoding(rs.getString(3)));
				dmb.setMs(encoding(rs.getString(4)));
			}
		}
		catch (Throwable e)
		{
			log.error(e.getMessage(), e);
		}
		finally
		{
			try
			{
				if (rs != null)
					rs.close();
				if (stmt != null)
					stmt.close();
			}
			catch (SQLException e)
			{
				e.printStackTrace();
			}
		}
		return dmb;
	}

	@Override
	public List<YHXXB> findAllYHXXB()
	{
		PreparedStatement stmt = null;
		ResultSet rs = null;
		List<YHXXB> userList = new ArrayList<YHXXB>();
		try
		{
			Connection conn = JdbcUtils.currentConnection();
			stmt = conn.prepareStatement("use systbase");
			stmt.executeUpdate();
			stmt.close();
			StringBuilder sql = new StringBuilder();
			sql.append("select A.user_id,A.user_ps,B.MS,A.FJ, A.status,C.MS as FJNAME");
			sql.append(" from YHXXB A,FJDMB B,DMB C");
			sql.append(" where A.user_id = B.DM");
			sql.append(" and A.FJ = B.FJ");
			sql.append(" and B.FJ = C.DM");
			sql.append(" and C.DMBMC = 'FJ'");
			sql.append(" and A.status = '0'");
			stmt = conn.prepareStatement(sql.toString());
			rs = stmt.executeQuery();
			userList = JdbcUtils.resultSetToList(YHXXB.class, rs);
			// for (YHXXB yhxxb : userList)
			// {
			// yhxxb.setFJNAME(this.encoding(yhxxb.getFJNAME()));
			// yhxxb.setFJ(this.encoding(yhxxb.getFJ()));
			// yhxxb.setMS(this.encoding(yhxxb.getMS()));
			// }
		}
		catch (Throwable e)
		{
			log.error(e.getMessage());
		}
		finally
		{
			try
			{
				if (rs != null)
					rs.close();
				if (stmt != null)
					stmt.close();
			}
			catch (SQLException e)
			{
				log.error(e.getMessage(), e);
			}
		}
		return userList;
	}

	public static void main(String[] args) throws UnsupportedEncodingException
	{
		DataServiceImpl ds = new DataServiceImpl();
		// Nsrxx Nsrxx = ds.getNsrxxByNsrdm("74000691");
		List<YHXXB> yhList = ds.findAllYHXXB();
		JdbcUtils.closeCurrent();
	}

}
